Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Collections.Generic
Imports Microsoft.Practices.EnterpriseLibrary.Data

Namespace DAL.Servicio
	Public Class PlanServicio

		Public Function Insertar(ByVal _Plan As BEL.Servicio.PlanServicio) As System.Boolean
			Dim db As Database = DatabaseFactory.CreateDatabase("ServCons")
			Dim cm As DbCommand = db.GetStoredProcCommand("[SER_I_PlanServicio]")
			Using cn As DbConnection = db.CreateConnection()
				cn.Open()
				Dim tx As DbTransaction = cn.BeginTransaction()
				Try
					db.AddInParameter(cm, "NO_ANIO", DbType.String, _Plan.NO_ANIO)
					db.AddInParameter(cm, "NO_MES", DbType.String, _Plan.NO_MES)
					db.AddInParameter(cm, "TX_DESCRIPCION", DbType.String, _Plan.TX_DESCRIPCION)
					Using dr As IDataReader = db.ExecuteReader(cm, tx)
						dr.Read()
						_Plan.CO_PLAN = Convert.ToInt16(dr("CO_PLAN"))
					End Using

					tx.Commit()
				Catch _ex As Exception
					tx.Rollback()
					Throw New Exception(_ex.Message, _ex.InnerException)
				Finally
					tx.Dispose()
					cm.Dispose()
					tx = Nothing
					cm = Nothing
					db = Nothing
				End Try
			End Using
			Return True
		End Function
		Public Function Modificar(ByVal _Plan As BEL.Servicio.PlanServicio) As System.Boolean
			Dim db As Database = DatabaseFactory.CreateDatabase()
			Dim cm As DbCommand = db.GetStoredProcCommand("[SER_U_PlanServicio]")
			Using cn As DbConnection = db.CreateConnection()
				cn.Open()
				Dim tx As DbTransaction = cn.BeginTransaction()
				Try
					db.AddInParameter(cm, "CO_PLAN", DbType.Int16, _Plan.CO_PLAN)
					db.AddInParameter(cm, "NO_ANIO", DbType.String, _Plan.NO_ANIO)
					db.AddInParameter(cm, "NO_MES", DbType.String, _Plan.NO_MES)
					db.AddInParameter(cm, "TX_DESCRIPCION", DbType.String, _Plan.TX_DESCRIPCION)
					db.ExecuteNonQuery(cm, tx)

					tx.Commit()
				Catch _ex As Exception
					tx.Rollback()
					Throw New Exception(_ex.Message, _ex.InnerException)
				Finally
					tx.Dispose()
					cm.Dispose()
					tx = Nothing
					cm = Nothing
					db = Nothing
				End Try
			End Using
			Return True
		End Function
		Public Function Listar(ByVal _Parametros As Object()) As DataTable
            Dim ds As New DataSet()
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_PlanServicio_Listado]")
            db.AddInParameter(cm, "NO_ANIO", DbType.String, Convert.ToString(_Parametros(0)))
            db.AddInParameter(cm, "NO_MES", DbType.String, Convert.ToString(_Parametros(1)))

            Try
                ds = db.ExecuteDataSet(cm)
                ds.Tables(0).TableName = "PLANES"
                Return ds.Tables(0)
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
        End Function
        Public Function Cargar(ByVal _Parametros As Object()) As BEL.Servicio.PlanServicio
            Dim ds As New DataSet()
            Dim _Plan As New BEL.Servicio.PlanServicio
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_PlanServicio]")
            db.AddInParameter(cm, "CO_PLAN", DbType.String, Convert.ToString(_Parametros(0)))

            Try
                Using dr As IDataReader = db.ExecuteReader(cm)
                    While dr.Read()
                        _Plan.EsNuevo = False
                        _Plan.CO_PLAN = Convert.ToInt32(dr("CO_PLAN"))
                        _Plan.NO_ANIO = Convert.ToString(dr("NO_ANIO"))
                        _Plan.NO_MES = Convert.ToString(dr("NO_MES"))
                        _Plan.TX_DESCRIPCION = Convert.ToString(dr("TX_DESCRIPCION")).Trim()
                    End While
                End Using

                Return _Plan
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
        End Function
        Public Function Imprimir(ByVal _Parametros As Object()) As DataTable
            Dim ds As New DataSet()
            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim cm As System.Data.Common.DbCommand = db.GetStoredProcCommand("[SER_S_PlanServicio_Reporte]")
            db.AddInParameter(cm, "NO_ANIO", DbType.String, Convert.ToString(_Parametros(0)))
            db.AddInParameter(cm, "NO_MES", DbType.Int32, Convert.ToInt32(_Parametros(1)))

            Try
                ds = db.ExecuteDataSet(cm)
                ds.Tables(0).TableName = "REPORTE"
                Return ds.Tables(0)
            Catch ex As System.Data.SqlClient.SqlException
                Throw New Exception(ex.Message, ex.InnerException)
            End Try
        End Function
        Public Function Existe(ByVal strAnio As String, ByVal strMes As String) As System.Boolean
            Dim ds As New DataSet
            Dim db As Database = DatabaseFactory.CreateDatabase("ServCons")
            Dim cm As DbCommand = db.GetStoredProcCommand("[SER_S_PlanServicio_Listado]")
            Using cn As DbConnection = db.CreateConnection()
                cn.Open()
                Dim tx As DbTransaction = cn.BeginTransaction()
                Try
                    db.AddInParameter(cm, "NO_ANIO", DbType.String, strAnio)
                    db.AddInParameter(cm, "NO_MES", DbType.String, strMes)
                    ds = db.ExecuteDataSet(cm)
                    If ds.Tables(0).Rows.Count = 0 Then
                        Return False
                    Else
                        Return True
                    End If
                Catch _ex As Exception
                    tx.Rollback()
                    Throw New Exception(_ex.Message, _ex.InnerException)
                Finally
                    tx.Dispose()
                    cm.Dispose()
                    tx = Nothing
                    cm = Nothing
                    db = Nothing
                End Try
            End Using
            Return True
        End Function
        Public Function Eliminar(ByVal intPlan As Int32) As System.Boolean
            Dim db As Database = DatabaseFactory.CreateDatabase("ServCons")
            Dim cm As DbCommand = db.GetStoredProcCommand("[SER_D_PlanServicio]")
            Using cn As DbConnection = db.CreateConnection()
                cn.Open()
                Dim tx As DbTransaction = cn.BeginTransaction()
                Try
                    db.AddInParameter(cm, "CO_PLAN", DbType.Int32, intPlan)
                    db.ExecuteNonQuery(cm, tx)
                    tx.Commit()
                Catch _ex As Exception
                    tx.Rollback()
                    Throw New Exception(_ex.Message, _ex.InnerException)
                Finally
                    tx.Dispose()
                    cm.Dispose()
                    tx = Nothing
                    cm = Nothing
                    db = Nothing
                End Try
            End Using
            Return True
        End Function
	End Class
End Namespace